/** Copyright 2014, Ross Mahony. Some rights reserved. See COPYING. */
package models

import play.api.db.slick.Config.driver.simple._
import scala.slick.lifted.Tag

case class Tip(id: Option[Long], summary: String, detail: String, customerId: Long)
case class TipComment(id: Option[Long], tipId: Long, customerId: Long, comment: String)
case class TipLike(tipId: Long, customerId: Long)

/** Tip entity. */
class TipTbl(tag: Tag) extends Table[Tip](tag, "TIP") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def summary = column[String]("TITLE", O.NotNull)
  def detail = column[String]("DETL", O.NotNull, O.DBType("VARCHAR(10000)"))
  def customerId = column[Long]("CUST_ID", O.NotNull)
  def * = (id.?, summary, detail, customerId) <> (Tip.tupled, Tip.unapply _)
}

/** TipComment entity. */
class TipCommentTbl(tag: Tag) extends Table[TipComment](tag, "TIP_CMNT") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def tipId = column[Long]("TIP_ID", O.NotNull)
  def customerId = column[Long]("CUST_ID", O.NotNull)
  def comment = column[String]("CMNT", O.NotNull, O.DBType("VARCHAR(2000)"))
  def * = (id.?, tipId, customerId, comment) <> (TipComment.tupled, TipComment.unapply _)
  def tip = foreignKey("TIP_COMMENT_TIP_FK", tipId, TipDAO.tips)(_.id)
}

/** TipLike entity. */
class TipLikeTbl(tag: Tag) extends Table[TipLike](tag, "TIP_VOTE") {
  def tipId = column[Long]("TIP_ID", O.NotNull)
  def customerId = column[Long]("CUST_ID", O.NotNull)
  def * = (tipId, customerId) <> (TipLike.tupled, TipLike.unapply _)
  def pk = primaryKey("TIP_LIKE_PK", (tipId, customerId))
  def tip = foreignKey("TIP_LIKE_TIP_FK", tipId, TipDAO.tips)(_.id)
}

/** Tip DAO */
object TipDAO {
  val tips = TableQuery[TipTbl]
  val comments = TableQuery[TipCommentTbl]
  val likes = TableQuery[TipLikeTbl]
  
 /**
  * Retrieve a [[models.Tip]] by the id
  *
  * @param id
  */
  def findById(id: Long)(implicit s: Session): Option[Tip] = {
    tips.where(_.id === id).firstOption  
  }

 /** Retrieve [[models.Tip]] with [[models.Customer]] */  
  def listWithCustomer(implicit s: Session): List[(Tip, Customer)] = {
    val query = for {
      tip <- tips
      customer <- CustomerDAO.customers if customer.id === tip.customerId
    } yield (tip, customer)

    query.list  
  }

 /** Retrieve [[models.TipComment]] with [[models.Customer]] */  
  def listCommentsWithCustomer(implicit s: Session): List[(TipComment, Customer)] = {
    val query = for {
      comment <- comments 
      customer <- CustomerDAO.customers if customer.id === comment.customerId
    } yield (comment, customer)

    query.list  
  }

 /** Retrieve all [[models.TipLike]] */
  def listLikes(implicit s: Session): Map[Long, List[Long]] = {
    likes.list.groupBy(_.tipId).map {
      case (tipId, customerId) => (tipId, customerId.map(_.customerId))
    }
      //likes.list.map(i => i.tipId -> i.customerId).toMap
  }

 /** Count all tips */
  def count(implicit s: Session): Int = Query(tips.length).first

 /**
  * Insert a new [[models.Tip]]
  *
  * @param tip
  */
  def insert(tip: Tip)(implicit s: Session) {
    tips.insert(tip)
  }

 /**
  * Insert a new [[models.Tip]] by ignoring auto inc column
  *
  * @param tip
  */
  def forceInsert(tip: Tip)(implicit s: Session) {
    tips.forceInsert(tip)
  }

 /**
  * Deletes a [[models.Tip]] 
  *
  * @param id
  */
  def delete(id: Long)(implicit s: Session) {
    tips.where(_.id === id).delete
  }

 /**
  * Insert a new [[models.TipComment]]
  *
  * @param comment
  */
  def insertComment(comment: TipComment)(implicit s: Session) = {
   (comments returning comments.map(_.id)) += comment
  }

 /**
  * Insert a new [[models.TipComment]] by ignoring auto inc column
  *
  * @param comment
  */
  def forceInsertComment(comment: TipComment)(implicit s: Session) {
    comments.forceInsert(comment)
  }

 /**
  * Deletes a [[models.TipComment]] 
  *
  * @param id
  */
  def deleteComment(id: Long)(implicit s: Session) {
    comments.where(_.id === id).delete
  }

 /**
  * Deletes all [[models.TipComment]] by tip id
  *
  * @param tipId
  */
  def deleteAllComments(tipId: Long)(implicit s: Session) {
    comments.where(_.tipId === tipId).delete
  }

 /**
  * Insert a new [[models.TipLike]]
  *
  * @param like
  */
  def insertLike(like: TipLike)(implicit s: Session) {
    likes.insert(like)
  }

 /**
  * Insert a new [[models.TipLike]] by ignoring auto inc column
  *
  * @param like
  */
  def forceInsertLike(like: TipLike)(implicit s: Session) {
    likes.forceInsert(like)
  }

 /**
  * Deletes all [[models.TipLike]] by tip id
  *
  * @param tipId
  */
  def deleteAllLikes(tipId: Long)(implicit s: Session) {
    likes.where(_.tipId === tipId).delete
  }

 /** Count tip [[models.TipLike]] */
  def countLikes(id: Long)(implicit s: Session): Int = Query(likes.where(_.tipId === id).length).first
 
 /** Delete all tips, comments and likes */
  def deleteAll(implicit s: Session) {
    likes.delete
    comments.delete
    tips.delete
  }


/** 
 * Drop the tip tables.
 * This is only used to reset the database, dropping tables as opposed to deleting them
 * because test data relies on sequence generated PK (autoInc) set out by Slick. 
 * I was using forceInsert in testdata and was working with H2 but PostgreSql didn't 
 * update the sequence that Slick defined.
 */ 
  def dropTipTables(implicit s: Session) {
    val ddl = tips.ddl ++ comments.ddl ++ likes.ddl
    ddl.drop
    ddl.create
  }  
  
}

